#！/bin/bash
hive=$HIVE_HOME/bin/hive
# 可以输入日期；如果未输入日期取昨天的时间
if [ -n "$1" ]
then
  do_date=$1
else
 do_date=`date -d "-1 day" +%F`
fi

# 定义要执行的SQL
sql="

with tmp as (

select device_id,gid,count(*) as cnt
from (
select 
	device_id ,dt,date_sub(dt,row_number () over (partition by device_id order by dt)) as gid
from dws.dws_member_start_day
	where dt <= '$do_date' and dt >= date_add('$do_date',-6)
) as aa group by device_id,gid
having cnt >=3
)

insert overwrite table ads.ads_member_active
partition(dt='$do_date')
select 
date_add('$do_date',-6),
count(1),
3
from tmp
;
"
$hive -e "$sql"
